package com.wangshuqi.jdbc.transaction;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * jdbc工具类 -- 可事务操作
 *
 * @author codewsq
 * @date 2021/8/26 0026 10:57
 */
public class JDBCTransactionUtil {

    /**
     * 获取connection数据库连接
     *
     * @return java.sql.Connection 数据库连接
     * @author codewsq
     * @date 2021/8/21 0021 13:37
     */
    public static Connection getConnection() throws Exception {
        // 1.加载配置文件
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        properties.load(is);

        // 2.读取配置信息
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");

        // 3.加载驱动
        Class.forName(driver);

        // 4.获取数据库连接
        Connection connection = DriverManager.getConnection(url, user, password);
        return connection;
    }

    /**
     * 关闭数据库连接
     *
     * @param connection 数据库连接
     * @return void
     * @author codewsq
     * @date 2021/8/21 0021 13:38
     */
    public static void closeConnection(Connection connection) {
        try {
            if (connection != null)
                connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    /**
     * 关闭数据库连接
     *
     * @param connection 数据库连接
     * @param statement  preparedStatement对象
     * @return void
     * @author codewsq
     * @date 2021/8/21 0021 13:38
     */
    public static void closeConnection(Connection connection, Statement statement) {
        try {
            if (statement != null)
                statement.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        try {
            if (connection != null)
                connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    /**
     * 关闭数据库连接
     *
     * @param connection 数据库连接
     * @param statement  preparedStatement对象实例
     * @param resultSet  结果集对象实例
     * @return void
     * @author codewsq
     * @date 2021/8/21 0021 13:38
     */
    public static void closeConnection(Connection connection, Statement statement, ResultSet resultSet) {
        try {
            if (resultSet != null)
                resultSet.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        try {
            if (statement != null)
                statement.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        try {
            if (connection != null)
                connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    /**
     * 增删改操作
     *
     * @param connection 数据库连接
     * @param sql        执行的sql语句
     * @param args       参数列表
     * @return int 操作条数
     * @author codewsq
     * @date 2021/8/21 0021 15:03
     */
    public static int saveOrUpdateOrDelete(Connection connection, String sql, Object... args) {
        int count = 0;
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql); // 预编译sql
            for (int i = 0; i < args.length; i++) { // 封装数据
                preparedStatement.setObject(i + 1, args[i]);
            }
            // 执行sql
            // 方式一：boolean execute()  如果sql是查询语句，则为true，如果是增删改，则为false
            /*preparedStatement.execute();*/
            // 方式二：int executeUpdate() 返回更新的条数
            count = preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 只关闭preparedStatement
            closeConnection(null, preparedStatement);
            return count;
        }
    }

    /**
     * 查询单条数据
     *
     * @param connection 数据库连接
     * @param clazz      查询类的class对象
     * @param sql        查询sql语句
     * @param args       查询条件
     * @return T 返回查询结果对象,没有结果则为 null
     * @author codewsq
     * @date 2021/8/21 0021 17:46
     */
    public static <T> T selectOne(Connection connection, Class<T> clazz, String sql, Object... args) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }
            resultSet = preparedStatement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            if (resultSet.next()) {
                T t = clazz.newInstance();
                for (int i = 0; i < columnCount; i++) {
                    String columnLabel = metaData.getColumnLabel(i + 1); // 获取结果集列名（查询时，sql语句中的列别名）
                    Field field = t.getClass().getDeclaredField(columnLabel); // 根据列名获取javabean对应的属性
                    field.setAccessible(true); // 开启访问权限
                    field.set(t, resultSet.getObject(i + 1)); // 给对应的javabean属性赋值
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeConnection(null, preparedStatement, resultSet);
        }
        return null;
    }

    /**
     * 查询多条数据
     *
     * @param connection 数据库连接
     * @param clazz      查询类的class对象
     * @param sql        查询sql语句
     * @param args       查询条件
     * @return T 返回查询结果集合,没有结果则为 new ArrayList<>()
     * @author codewsq
     * @date 2021/8/21 0021 18:04
     */
    public static <T> List<T> selectList(Connection connection, Class<T> clazz, String sql, Object... args) {
        List<T> result = new ArrayList<>();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }

            resultSet = preparedStatement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            while (resultSet.next()) {
                T t = clazz.newInstance();
                for (int i = 0; i < columnCount; i++) {
                    String columnLabel = metaData.getColumnLabel(i + 1); // 获取结果集列名（查询时，sql语句中的列别名）
                    Field field = t.getClass().getDeclaredField(columnLabel); // 根据列名获取javabean对应的属性
                    field.setAccessible(true); // 开启访问权限
                    field.set(t, resultSet.getObject(i + 1)); // 给对应的javabean属性赋值
                }
                result.add(t);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            closeConnection(null, preparedStatement, resultSet);
        }
        return result;
    }
}
